package com.jic.product.impl;

import com.jic.common.base.vo.Page;
import com.jic.common.base.vo.PageResult;
import com.jic.common.base.vo.RestResult;
import com.jic.product.entity.*;
import com.jic.product.manage.request.ProductBrandRequest;
import com.jic.product.manage.request.ProductPropertyValueRequest;
import com.jic.product.manage.request.ProductUnitsRequest;
import com.jic.product.manage.request.ProductUploadExceptionRequest;
import com.jic.product.manage.response.ProductBrandResponse;
import com.jic.product.manage.response.ProductUnitsResponse;
import com.jic.product.manage.response.ProductUploadExceptionResponse;
import com.jic.product.mapper.*;
import com.jic.product.service.ProductUploadExceptionService;
import com.jic.product.util.ExcelImportUtils;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 商品导入异常
 *
 * @author :
 * @version : v1.0.0
 * @email :
 * @since : 2020-04-27 02:26:45
 */
@Service
public class ProductUploadExceptionServiceImpl implements ProductUploadExceptionService {
    @Value("${file.upload.path}")
    private String path;
    @Value("${file.cdn.url}")
    private String url;
    @Autowired
    private ProductUploadExceptionMapper productUploadExceptionMapper;
    @Autowired
    private ProductBrandMapper productBrandMapper;
    @Autowired
    private ProductUnitsMapper productUnitsMapper;
    @Autowired
    private ProductMapper productMapper;
    @Autowired
    private ProductGuaranteeMapper productGuaranteeMapper;
    @Autowired
    private ProductPriceMapper productPriceMapper;
    @Autowired
    private ProductPropertyNameMapper productPropertyNameMapper;

    @Autowired
    private ProductGuaranteeRelationMapper productGuaranteeRelationMapper;

    @Autowired
    private ProductPropertyNameRelationMapper productPropertyNameRelationMapper;
    @Autowired
    private ProductPropertyValueMapper productPropertyValueMapper;
    @Autowired
    private ProductPropertyValueRelationMapper productPropertyValueRelationMapper;

    @Override
    public long insert(ProductUploadException productUploadException) {
        long flag = productUploadExceptionMapper.insert(productUploadException);
        return flag;
    }

    @Override
    public long delete(Long id) {
        return productUploadExceptionMapper.delete(id);
    }

    @Override
    public long update(ProductUploadException productUploadException) {
        return productUploadExceptionMapper.update(productUploadException);
    }

    @Override
    public ProductUploadException queryByPrimaryKey(Long id) {
        return productUploadExceptionMapper.queryByPrimaryKey(id);
    }

    @Override
    public List<ProductUploadException> query(ProductUploadException productUploadException) {
        return productUploadExceptionMapper.query(productUploadException);
    }

    @Override
    public RestResult<PageResult<ProductUploadExceptionResponse>> queryPage(ProductUploadExceptionRequest productUploadExceptionRequest, Page pageQuery) {
        //计算下标
        int startIndex = (pageQuery.getStart() - 1) * pageQuery.getLimit();
        List<ProductUploadExceptionResponse> list = productUploadExceptionMapper.queryPage(startIndex, pageQuery.getLimit(), productUploadExceptionRequest);
        long count = productUploadExceptionMapper.queryPageCount(productUploadExceptionRequest);
        PageResult pageResult = new PageResult();
        pageResult.setRows(list);
        pageResult.setTotal(count);
        return RestResult.success(pageResult);
    }


    /**
     * 批量导入商品
     *
     * @param fileName
     * @param file
     * @return
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public RestResult batchUploadProduct(String fileName, MultipartFile file, Long categoryId, Integer typeOfProduct, Integer type,Long deptId) throws Exception {
        //验证EXCEL文件
        if (!ExcelImportUtils.validateExcel(fileName)) {
            return RestResult.error("9999", "上传文件格式不正确");
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        //商品数据
        Sheet sheet = null;
        //2003的excel
        if (ExcelImportUtils.isExcel2003(fileName)) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        sheet = wb.getSheetAt(0);
        if (sheet == null) {
            return RestResult.error("9999", "数据不存在");
        }
        if (categoryId == null) {
            return RestResult.error("9999", "类目id为空");
        }
        if (typeOfProduct == null) {
            return RestResult.error("9999", "商品形式为空");
        }
        if (type == null) {
            return RestResult.error("9999", "商品类型为空");
        }
        //异常数据保存
        List<Row> rowList = new ArrayList<>();
        List<Row> headerList = new ArrayList<>();
        //获取总列数
        int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
        //获取属性列数
        int columnPropertyNum = coloumNum - 19;
        //成功处理数量
        int susccessTotal = 0;
        //失败处理数量
        int failTotal = 0;
        //商品保障数组
        String[] guarantee = null;
        //销售区数组
        String[] saleArea = null;
        //表头行
        Row headersRow = sheet.getRow(0);
        headersRow.createCell(coloumNum + 1).setCellValue("上传结果");
        rowList.add(headersRow);
        //类目存在属性
        List<Map<Integer, Long>> propertyName = new ArrayList<>();
        List<Integer> columnIndex = new ArrayList<>();
        if (columnPropertyNum > 0) {
            for (int j = 19; j < coloumNum; j++) {
                Cell cell = headersRow.getCell(j);
                Map<Integer, Long> map = new HashMap<>();
                if (cell != null) {
                    ProductPropertyName productPropertyName = new ProductPropertyName();
                    productPropertyName.setName(cell.getStringCellValue());
                    List<ProductPropertyName> productPropertyNameList = productPropertyNameMapper.query(productPropertyName);
                    if (CollectionUtils.isNotEmpty(productPropertyNameList)) {
                        map.put(j, productPropertyNameList.get(0).getId());
                        propertyName.add(map);
                        columnIndex.add(j);
                    }
                }
            }
        }
        //r = 2 表示从第三行开始循环 如果你的第三行开始是数据
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
            //通过sheet表单对象得到 行对象
            Row row = sheet.getRow(r);
            if (row == null) {
                failTotal++;
                continue;
            }
            //商品基本信息
            Product product = new Product();
            //商品价格
            ProductPrice productPrice = new ProductPrice();
            //是否全满足
            Boolean flag = true;
            //属性值数据
            List<Map<Integer, String>> propertyNamvalue = new ArrayList<>();
            //共coloumNum列表数据
            for (int j = 0; j < coloumNum; j++) {
                Cell cell = row.getCell(j);
                //商品名称
                if (j == 0) {
                    if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                        row.createCell(coloumNum + 1).setCellValue("商品名称不存在");
                        rowList.add(row);
                        failTotal++;
                        flag = false;
                        break;
                    } else {
                        product.setChineseName(cell.toString());
                    }
                }
                //商品编码
                if (j == 1) {
                    if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                        row.createCell(coloumNum + 1).setCellValue("商品编码不存在");
                        rowList.add(row);
                        failTotal++;
                        flag = false;
                        break;
                    } else {
                        product.setCode(cell.getStringCellValue());
                    }
                }
                //第三方编码
                if (j == 2) {
                    if (cell != null) {
                        product.setThirdProductCode(cell.getStringCellValue());
                    }

                }
                //副标题
                if (j == 3) {
                    if (cell != null) {
                        product.setSubtitle(cell.toString());
                    }

                }
                //商品品牌
                if (j == 4) {
                    if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                        row.createCell(coloumNum + 1).setCellValue("商品名称不存在");
                        rowList.add(row);
                        failTotal++;
                        flag = false;
                        break;
                    } else {
                        ProductBrandRequest productBrandRequest = new ProductBrandRequest();
                        productBrandRequest.setName(cell.getStringCellValue());
                        List<ProductBrandResponse> list = productBrandMapper.query(productBrandRequest);
                        if (CollectionUtils.isEmpty(list)) {
                            row.createCell(coloumNum + 1).setCellValue("商品名称不存在");
                            rowList.add(row);
                            failTotal++;
                            flag = false;
                            break;
                        } else {
                            product.setBrandId(list.get(0).getId());
                        }
                    }
                }
                //主计量单位
                if (j == 5) {
                    if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                        row.createCell(coloumNum + 1).setCellValue("主计量单位不存在");
                        rowList.add(row);
                        failTotal++;
                        flag = false;
                        break;
                    } else {
                        ProductUnitsRequest productUnitsRequest = new ProductUnitsRequest();
                        productUnitsRequest.setName(cell.toString());
                        List<ProductUnitsResponse> list = productUnitsMapper.query(productUnitsRequest);
                        if (CollectionUtils.isEmpty(list)) {
                            row.createCell(coloumNum + 1).setCellValue("主计量单位不存在");
                            rowList.add(row);
                            failTotal++;
                            flag = false;
                            break;
                        } else {
                            product.setSaleCalcUnitId(list.get(0).getId());
                        }
                    }
                }
                //商品条码
                if (j == 6) {
                    if (cell != null) {
                        product.setBarCode(cell.toString());
                    }
                }
                //货号
                if (j == 7) {
                    if (cell != null) {
                        product.setArtNo(cell.toString());
                    }
                }
                //产地
                if (j == 8) {
                    if (cell != null) {
                        product.setPlaceOriginName(cell.toString());
                    }

                }
                //净重起
                if (j == 9) {
                    if (cell != null) {
                        product.setNetWeight(new BigDecimal(cell.toString()));
                    }

                }
                //净重止
                if (j == 10) {
                    if (cell != null) {
                        product.setNetWeightStart(new BigDecimal(cell.toString()));
                    }

                }
                //支持发票
                if (j == 11) {
                    if (cell != null) {
                        if (cell.toString().equals("是")) {
                            product.setInvoice(0);
                        } else {
                            product.setInvoice(1);
                        }
                        ;
                    } else {
                        product.setInvoice(1);
                    }
                }
                //增票
                if (j == 12) {

                }
                //强制发票
                if (j == 13) {

                }
                //商品保障
                if (j == 14) {
                    if (cell != null) {
                        guarantee = cell.getStringCellValue().split(",");
                    }
                }
                //销售区域编码
                if (j == 15) {
                    if (cell != null) {
                        saleArea = cell.getStringCellValue().split(",");
                    }
                }
                //零售价
                if (j == 16) {
                    if (cell == null) {
                        row.createCell(coloumNum + 1).setCellValue("零售价不存在");
                        rowList.add(row);
                        failTotal++;
                        flag = false;
                        break;
                    } else {
                        productPrice.setSalePriceWithTax(new BigDecimal(cell.getNumericCellValue()));
                    }
                }
                //采购价
                if (j == 17) {
                    if (cell != null) {
                        productPrice.setPurchasePriceWithTax(new BigDecimal(cell.getNumericCellValue()));
                    }
                }
                //团购价
                if (j == 18) {
                    if (cell != null) {
                        productPrice.setPurchasePriceWithTax(new BigDecimal(cell.getNumericCellValue()));
                    }
                }
                //属性
                if (columnPropertyNum > 0) {
                    for (int i = 0; i < columnIndex.size(); i++) {
                        if (columnIndex.get(i) == j) {
                            Map<Integer, String> mapValue = new HashMap<>();
                            mapValue.put(j, cell.toString());
                            propertyNamvalue.add(mapValue);
                        }
                    }
                }
            }

            if (flag) {
                //保存产品基本信息
                product.setType(type);
                product.setTypeOfProduct(typeOfProduct);
                product.setCategoryId(categoryId);
                product.setDeptId(deptId);
                productMapper.insert(product);
                //保存商品类目属性
                if (CollectionUtils.isNotEmpty(propertyName)) {
                    propertyName.forEach(item -> {
                        item.forEach((key, value) -> {
                            ProductPropertyNameRelation productPropertyNameRelation = new ProductPropertyNameRelation();
                            productPropertyNameRelation.setAttNameId(value);
                            productPropertyNameRelation.setProductId(product.getId());
                            productPropertyNameRelationMapper.insert(productPropertyNameRelation);
                            propertyNamvalue.forEach(item1 -> {
                                item1.forEach((key1, value1) -> {
                                    if (key1 == key) {
                                        if (StringUtils.isNotEmpty(value1)) {
                                            ProductPropertyValueRelation productPropertyValueRelation = new ProductPropertyValueRelation();
                                            ProductPropertyValueRequest productPropertyValueRequest = new ProductPropertyValueRequest();
                                            productPropertyValueRequest.setName(value1);
                                            List<ProductPropertyValue> productPropertyValueList = productPropertyValueMapper.query(productPropertyValueRequest);
                                            if (CollectionUtils.isNotEmpty(productPropertyValueList)) {
                                                productPropertyValueRelation.setAttValueId(productPropertyValueList.get(0).getId());
                                                productPropertyValueRelation.setProductAttNameId(productPropertyNameRelation.getId());
                                                productPropertyValueRelationMapper.insert(productPropertyValueRelation);
                                            }

                                        }
                                    }
                                });
                            });
                        });
                    });
                }
                //保存产品保障
                if (guarantee != null) {
                    for (int i = 0; i < guarantee.length; i++) {
                        ProductGuarantee productGuarantee = new ProductGuarantee();
                        productGuarantee.setGuaranteeName(guarantee[i]);
                        List<ProductGuarantee> productGuaranteeList = productGuaranteeMapper.query(productGuarantee);
                        if (CollectionUtils.isNotEmpty(productGuaranteeList)) {
                            ProductGuaranteeRelation productGuaranteeRelation = new ProductGuaranteeRelation();
                            productGuaranteeRelation.setGuaranteeId(productGuaranteeList.get(0).getId());
                            productGuaranteeRelation.setProductId(product.getId());
                            productGuaranteeRelationMapper.insert(productGuaranteeRelation);
                        }
                    }
                }
                //保存产品价格
                productPrice.setProductId(product.getId());
                productPriceMapper.insert(productPrice);
                //保存商品销售区域
                susccessTotal++;
            }
        }
//        String url = "";
//        if (CollectionUtils.isNotEmpty(rowList)) {
//            url = this.exceptionProduct(rowList);
//        }
        ProductUploadException productUploadException = new ProductUploadException();
        productUploadException.setName(fileName);
//        productUploadException.setUrl(url);
        productUploadException.setType(0);
        productUploadException.setStatus(1);
        productUploadException.setTotal(sheet.getLastRowNum());
        productUploadException.setSuccessTotal(susccessTotal);
        productUploadException.setFailTotal(failTotal);
        this.insert(productUploadException);
        return RestResult.success(true);
    }


    /**
     * 异常商品生成
     *
     * @param rowList
     * @param rowList
     * @return
     */
    public String exceptionProduct(List<Row> rowList) throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        List<String> headers = new ArrayList<>();
        List<Object> Body = new ArrayList<>();
        rowList.get(0).forEach(item -> {
            headers.add(item.getStringCellValue());
        });

        HSSFSheet sheetSolution = wb.createSheet("异常数据");
        HSSFRow rows = null;
        HSSFCell cells = null;
        //创建表头行
        rows = sheetSolution.createRow(0);
        for (int i = 0; i < headers.size(); i++) {
            cells = rows.createCell(i);
            cells.setCellValue(headers.get(i));
            sheetSolution.setColumnWidth(i, 5000);
        }
        FileOutputStream out = new FileOutputStream(path + "失败数据" + "." + "xls");
        out.write(wb.getBytes());
        out.close();
        String excelUrl = url + "失败数据" + "." + "xls";
        return excelUrl;
    }


}